Amazon Redshift DB開発者ガイド – クエリパフォーマンスチューニング(1).実行計画の分析
勢い余ってしまい本日3本目のエントリ投下となってしまいましたw Amazon Redshift DB開発者ガイド、当エントリはデータのロード処理から離れて『実行計画の分析』というテーマについて。掘り下げて行きます。
目次
実行計画の分析
プロセスに異常に長い時間が掛かる場合は、実行計画を調べる事によってその性能を向上出来る機会を発見する事が出来るかも知れません。このセクションでは、Amazon Redshiftのクエリを最適化する機会を見つける為に、どのように実行計画を表示し、利用していくかについて説明します。
実行計画を作成するには、以下例のようにEXPLAINコマンドの後に実際のクエリテキストを実行します。
explain select avg(datediff(day, listtime, saletime)) as avgwait
from sales, listing where sales.listid = listing.listid;
QUERY PLAN
XN Aggregate (cost=6350.30..6350.31 rows=1 width=16)
-> XN Hash Join DS_DIST_NONE (cost=47.08..6340.89 rows=3766 width=16)
Hash Cond: ("outer".listid = "inner".listid)
-> XN Seq Scan on listing (cost=0.00..1924.97 rows=192497 width=12)
-> XN Hash (cost=37.66..37.66 rows=3766 width=12)
-> XN Seq Scan on sales (cost=0.00..37.66 rows=3766 width=12)
実行計画では、以下の情報を提供します。
- 実行エンジンが遂行している手順が何なのか、下から上に読んでいます。
- 各手順が実行する操作の種類。この例では、操作は下から読んでハッシュシーケンシャルスキャン→シーケンシャルスキャン→ハッシュ結合→集計、となります。操作については、このセクションの後半で説明します。
- どのテーブルのカラムが各手順で使用されているか。
- どのくらいのデータが各手順で処理されているか。(行及びデータ幅の数はバイト単位)
- 操作の相対的なコスト。
考慮すべき最初の事は、各手順のコストです。コストはプラン内の手順の相対実行時間を比較した尺度となっています。これは、実際の実行時間やメモリ消費量に関する正確な情報を提供しているものではありませんし、また実行計画の間に意味のある比較を提供していません。がしかし、これらによってクエリ内のどの手順が最もリソースを表示しているかの指標を得る事が出来ます。高いコストを要している手順を識別する事は、あなたがコストを削減する機会を探し始める出発点を提供する事になります。
EXPLAINコマンドは、実際にクエリを発行しません。出力には、クエリが現在の動作条件下で実行された場合Amazon Redshiftが実行する計画のみが含まれています。幾つかの方法でテーブルのスキーマを変更した場合、またテーブル内のデータを変更し、再度ANALYZEを実行して統計的なメタデータを更新すると、実行計画が異なってくる場合があります。
注意：
所有者のあなただけが、データ操作言語(DML)でEXPLAINを実行できます。
データ定義言語(DDL)やデータベース操作等、他のSQLコマンドに対してEXPLAINを
使用すると、EXPLAIN操作は失敗します。
EXPLAINは以下のコマンドにのみ、使用が可能です。
- SELECT
- SELECT INTO
- CREATE TABLE AS(CTAS)
- INSERT
- UPDATE
- DELETE
EXPLAINの出力は、データ分散及び並行クエリ実行についての他の側面に関する制限された情報を提供します。以下の操作を実行するために、システムテーブルやビュー、特にSTLテーブルやSVL_QUERY_SUMMARYビューの幾つかを使用します。
- 実際の実行統計を返す
- クエリプラン内の手順の動作を分離する
- クエリのアクティビティを監視
- データ分散スキューを検出
実行計画とシステムビューを使用する方法の詳細については、システムビューに対してクエリプランをマッピングをご参照ください。以下2つの方法のいずれかで、クエリに対するEXPLAINの出力を調べる事が出来ます。
単一クエリに対し、明示的にEXPLAINコマンドを使用：
explain select avg(datediff(day, listtime, saletime)) as avgwait
from sales, listing where sales.listid = listing.listid;
QUERY PLAN
XN Aggregate (cost=6350.30..6350.31 rows=1 width=16)
-> XN Hash Join DS_DIST_NONE (cost=47.08..6340.89 rows=3766 width=16)
Hash Cond: ("outer".listid = "inner".listid)
-> XN Seq Scan on listing (cost=0.00..1924.97 rows=192497 width=12)
-> XN Hash (cost=37.66..37.66 rows=3766 width=12)
-> XN Seq Scan on sales (cost=0.00..37.66 rows=3766 width=12)
STL_EXPLAINテーブルを照会 以前実行したクエリを実行すると仮定し、そのクエリIDを10とします。あなたは、EXPLAINコマンドが返す情報の同じ種類を表示するためにSTL_EXPLAINテーブルに問い合わせる事が出来ます。：
select query,nodeid,parentid,substring(plannode from 1 for 30),
substring(info from 1 for 20) from stl_explain
where query=10 order by 1,2;
query | nodeid | parentid | substring | substring
------+--------+----------+---------------------+---------------------
10 | 1 | 0 | XN Aggregate (cost=6350.30... |
10 | 2 | 1 | -> XN Merge Join DS_DIST_NO | Merge Cond: ("outer"
10 | 3 | 2 | -> XN Seq Scan on lis |
10 | 4 | 2 | -> XN Seq Scan on sal |
(4 rows
シンプルなEXPLAINの実行例
以下の例は、EVENTテーブルに対するシンプルなGROUP BYクエリのシンプルなEXPLAIN出力を示しています。
explain select eventname, count(*) from event group by eventname;
QUERY PLAN
-------------------------------------------------------------------
XN HashAggregate (cost=131.97..133.41 rows=576 width=17)
-> XN Seq Scan on event (cost=0.00..87.98 rows=8798 width=17)
(2 rows)
実行順序は『下から上』となります。最初の作業は、EVENTテーブルのスキャンを伴います。スキャン実施後、HashAggregateオペレータがGROUP BYリクエストを適用します。EXPLAINの出力に示される計画はシンプル且つ高レベルでクエリ実行内容を閲覧する事が出来ます。(※並列クエリ処理の詳細を説明する、という事ではありません) 例えば、Amazon Redshiftは並列計算ノードにある各データスライス上でHashAggregate操作を並列実行します。そしてその後はHashAggregate操作の中間結果の 2番目を実行します。詳細なレベルを表示するには、クエリ自体を実行し、SVL_QUERY_SUMMARYビューを照会する必要があります。
先の実行結果の要素について、以下の表で解説します。
コスト / cost
コストは計画内の操作を比較するのに有用である相対値です。クエリプラン内のコストは計画を読むに従い累積されて行きます。この例では、HashAggregateのコスト数値は(133.41)となっています。これは以下のシーケンシャルコスト(87.98)で主に構成されています。
コストエントリは、2つの問いに対する回答を示します。
- 最初の行で返している相対的なコストは何か(スタートアップコスト)？ (※この例では、両方のオペレータで0.00)
- 操作を完了している相対的なコストは何か？ (※この例ではスキャンのために87.98掛かっている)
注意：
コストは推定時間値ではありません。また秒やミリ秒単位で測定されている訳でもありません。
行 / rows
返される行数の期待値。この例では、スキャンは8798行を返す事が期待されます。HashAggregateオペレータは576行を返す事が期待されます。(重複イベント名が破棄される場合)
注意：
行推定値はANALYZEコマンドによって生成された使用可能な統計に基づいています。
ANALYZEが最近実行されていない場合、推定内容は信頼性が低いものになるでしょう。
幅 / width
バイト単位の平均行の推定値。この例では、平均の行は17バイト幅であると推定されます。
EXPLAINオペレータ
以下のリストでは、EXPLAINの出力に最も頻繁に表示される演算子について説明します。演算子の完全なリストについては、SQLコマンドリファレンスのEXPLAINをご参照ください。
スキャン演算子 / Scan operator
シーケンシャルスキャン演算子(シーケンシャルスキャン)はリレーショナルスキャン又はテーブルスキャン、演算子です。シーケンシャルスキャンでは最初から最後まで順番にテーブル全体をスキャンして、全ての行のクエリ制約(WHERE句)で評価します。シーケンシャルスキャンでは、列でテーブルをスキャンします。
結合演算子 / Join operators
Amazon Redshiftクエリはテーブルが結合される物理設計、結合に必要なデータの場所、クエリ自体の特定の要件に応じて異なる結合演算子を使います。以下の演算子が利用可能です。
- Nested Loop - 少なくとも最適な結合、Nested Loopはクロス結合(デカルト積)と幾つかの不平等結合に対し主に使われています。
- Hash Join and Hash - ネストされたループ結合よりも通常は高速に、内部結合と右辺・左辺外部結合のためにハッシュ結合とハッシュが使用されます。この演算子は、テーブル結合の際に、(両方とも)分散キーでもソートキーでも無いテーブルの列を結合する場合に一般的に使用されます。ハッシュ演算子は、結合で内部テーブルのハッシュテーブルを作成します：ハッシュ結合演算子は外側のテーブルを読み取り、結合列をハッシュし、内部ハッシュテーブル内の一致を検出します。
- Merge Join - この演算子はNested Loop結合よりも一般的に早く、内側と外側の結合のために使用されます。両方のテーブルの連結列が分散キーとソートキーの両方である場合、これは一般的に使用されます。オペレーターは2つのソートテーブルを読み取り、一致する行を検索します。
集約クエリ演算子 / Aggregate query operators
以下の演算子は、集計関数やGROUP BYを含むクエリで使用されています。
- Aggregate - スカラー集計関数に使用します。
- HashAggregate - ソートされていないグループ化された集計機能のために使用します。
- GroupAggregate - 並べ替え、グループ化、集計関数に使用します。
ソート演算子 / Sort operators
クエリが結果セットをソートしたり、マージしなければならない時に以下の演算子が使用されています。
- Sort - UNIONクエリや結合を求められるソート、SELECT DISTINCTクエリ、ウインドウ関数のようなORDER BY句や他のソート操作を評価。
- Merge - 並行操作から導き出された中間の並べ替え結果による、最終的なソートされた結果を生成。
組み合わせ、交差、除外演算子 / UNION, INTERSECT, and EXCEPT operators
以下の演算子はUNION, INTERSECT, EXCEPTを使用する操作を伴うクエリにて使用されています。
- Subquery - スキャンと追加は、UNIONクエリを実行するために使用します。
- Hash Intersect Distinct and Hash Intersect All - INTERSECTとINTERSECT ALLのために使用します。
- SetOp Except - EXCEPT(又はMINUS)を実行するするために使用します。
その他の演算子 / Other operators
以下の演算子は、分類が難しいものではありますが、日常実行されるEXPLAIN出力において頻繁に表示されます。
- Unique - SELECT DISTINCTクエリとUNIONクエリの重複を排除します。
- Limit - LIMIT句を評価します。
- Window - ウインドウ関数を実行します。
- Result - 任意のテーブルへのアクセスを伴わないスカラー関数を実行します。
- Subplan - 特定のサブクエリに使用します。
- Network - リーダーノードに中韓結果を送信します。
- Materialize - ネストされたループへの入力行を保存・結合し、幾つかのマージ結合を行います。
Joinの例
EXPLAIN出力は、内部結合及び外部結合の参照を公開しています。まず内部テーブルが最初にスキャンされます。これは一致し見極められたテーブルです。それは通常メモリに保持され、ソースハッシュテーブルであり、可能であれば(2つは)結合されたより小さいテーブルとなります。外部テーブルは内部テーブルと照合する行のソースです。これは通常、オンザフライでディスクから読み出されます。クエリのFROM句のテーブルの順序は、どのテーブルが内部でどのテーブルが外部かという点を定めるものではありません。
結合のEXPLAIN出力もデータ再分配の為の方法を指定します。(結合を促進するためにどうやってクラスタの周りのデータを移動させるか) このデータ移動は、ブロードキャストまたは再分散する事が出来ます。ブロードキャストでは、結合の一方の側からのデータ値は、各計算ノードから全ての他の計算ノードにコピーされ、全ての計算ノードはデータの完全なコピーで終わります。再分配において、参加しているデータ値は、現在のスライスから新しいスライス(異なるノード上)に送信されます。データは、その分散キーが参加列のいずれかである場合、結合に参加している他のテーブルの分散キーに一致させるために 典型的に再分配されます。テーブルのどちらも参加のいずれかの列に分散キーがある場合は、両方のテーブルのどちらかが配布されたり、内部表がすべてのノードにブロードキャストされます。
結合の為のEXPLAIN出力には以下の属性が表示されます。
- DS_BCAST_INNER
- 全ての計算ノードに全体の内部テーブルのコピーをブロードキャストします。
- DS_DIST_NONE
- テーブルが配布されていません：対応するスライスをノード間でデータを移動する事無く接合しているので、結合併置は可能です。
- DS_DIST_INNER
- 内部テーブルが配布されています。
- DS_DIST_BOTH
- 両方のテーブルが配布されています。
結合のサンプル
これらの例は、問合せプランナが選択した異なる結合アルゴリズムを示します。これらの特定のケースでは、クエリプランの選択肢はテーブルの物理設計に依存します。
2つのテーブルをハッシュ結合
以下のクエリはEVENTとCATEGORYをCATIDで結合します。CATID列はカテゴリのものでは無く、イベントのdistkey及びsortkeyです。ハッシュ結合はEVENTテーブルを外部テーブルとして、CATEGORYテーブルを内部テーブルとして実行されます。CATEGORYは小さいテーブルなので、プランナーはクエリ処理中(DC_BCAST_INNER)の計算ノードにそのコピーをブロードキャストします。この例では、結合コストが計画の累積コストの大半を占めています。
explain select * from category, event where category.catid=event.catid;
QUERY PLAN
-------------------------------------------------------------------------
XN Hash Join DS_BCAST_INNER (cost=0.14..6600286.07 rows=8798 width=84)
Hash Cond: ("outer".catid = "inner".catid)
-> XN Seq Scan on event (cost=0.00..87.98 rows=8798 width=35)
-> XN Hash (cost=0.11..0.11 rows=11 width=49)
-> XN Seq Scan on category (cost=0.00..0.11 rows=11 width=49)
(5 rows)
注意：
EXPLAIN出力の演算子の整列インデントは、これらの操作がお互いに依存せずに
並行して開始する事が出来ると言う事を示しています。
EVENTテーブルとハッシュ操作でスキャンが並んでいるものの、この場合だと
ハッシュ操作が完全に完了するまで、EVENTスキャンは待たなければなりません。
2つのテーブルをマージ結合
以下のクエリは前の例と同じ構造を持っていますが、SALESとLISTINGテーブルをLISTIDで結合しています。この列は、両方の表のdistkeyでありsortkeyです。マージ結合が選択され、データの再配布(DS_DIST_NONE)は求められる事はありません。
explain select * from sales, listing where sales.listid = listing.listid;
QUERY PLAN
-----------------------------------------------------------------------------
XN Merge Join DS_DIST_NONE (cost=0.00..127.65 rows=3766 width=97)
Merge Cond: ("outer".listid = "inner".listid)
-> XN Seq Scan on sales (cost=0.00..37.66 rows=3766 width=53)
-> XN Seq Scan on listing (cost=0.00..1924.97 rows=192497 width=44)
(4 rows)
[/sq]
<p>この例では、同じクエリ内で結合の異なる種類を示しています。前の例と同様に、SALESとLISTINGはマージ結合されていますが、3番目のテーブル、EVENTはマージ結合の結果とハッシュ結合する必要があります。繰り返しになりますが、ハッシュ結合はブロードキャストコストが発生します。</p>
explain select * from sales, listing, event
where sales.listid = listing.listid and sales.eventid = event.eventid;
QUERY PLAN
----------------------------------------------------------------------------
XN Hash Join DS_DIST_OUTER (cost=2.50..414400186.40 rows=740 width=440)
Outer Dist Key: "inner".eventid
Hash Cond: ("outer".eventid = "inner".eventid)
-> XN Merge Join DS_DIST_NONE (cost=0.00..26.65 rows=740 width=104)
Merge Cond: ("outer".listid = "inner".listid)
-> XN Seq Scan on listing (cost=0.00..8.00 rows=800 width=48)
-> XN Seq Scan on sales (cost=0.00..7.40 rows=740 width=56)
-> XN Hash (cost=2.00..2.00 rows=200 width=336)
-> XN Seq Scan on event (cost=0.00..2.00 rows=200 width=336)
(11 rows)
結合、集計、ソートの例
以下のクエリではSALESとEVENTテーブルのハッシュ結合を実行しています。初期ソート演算子は計算ノードで並列に実行し、ネットワークオペレータはマージオペレータが最終的な並べ替え結果を生成するリーダーノードに結果を送信します。
explain select eventname, sum(pricepaid) from sales, event
where sales.eventid=event.eventid group by eventname
order by 2 desc;
QUERY PLAN
---------------------------------------------------------------------------------
XN Merge(cost=1000088800178.99..1000088800179.49 rows=200 width=330)
Merge Key: sum(sales.pricepaid)
->XN Network (cost=1000088800178.99..1000088800179.49 rows=200 width=330)
Send to leader
-> XN Sort(cost=1000088800178.99..1000088800179.49 rows=200 width=330)
Sort Key: sum(sales.pricepaid)
-> XN HashAggregate(cost=88800170.85..88800171.35 rows=200 width=330)
-> XN Hash Join DS_DIST_INNER(cost=9.25..880016.15 rows=740 width=330)
Inner Dist Key: sales.eventid
Hash Cond: ("outer".eventid = "inner".eventid)
-> XN Seq Scan on event(cost=0.00..2.00 rows=200 width=322)
-> XN Hash (cost=7.40..7.40 rows=740 width=16)
-> XN Seq Scan on sales(cost=0.00..7.40 rows=740 width=16)
(13 rows)
システムビューに対してクエリプランをマッピング
計画の説明では、あなたが必要とする全ての詳細を持っていません。各クエリの実行手順と統計は、システムビューのSVL_QUERY_SUMMARYとSVL_QUERY_REPORTに記録されます。これらのビューには、EXPLAIN出力より粒度の細かいレベルでのクエリのアクティビティをキャプチャし、クエリのアクティビティを監視するために使用できるメトリックが含まれています。
クエリの完全実行プロファイルを勉強するには、最初のクエリを実行し、クエリにEXPLAINコマンドを実行し、EXPLAINの出力結果をシステムビューへマッピングします。例えば、分散キーのために選択肢を再評価する必要がある事を示している可能性のある、分散スキューを検出するシステムビューを使用する事が出来ます。
